﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using csRebides;
using Mono.Data.Sqlite; 

namespace csRebides
{
    class subFunction
    {

       /* »»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»
        * here start the methods of this class
        * »»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»
       */

       /*
       * ###################################################################################
       * Function to get the name of the first 3 degrees with more teatchers in the 'y' year
       * if you want all the establishements remove LIMIT 3 from the end of the query
       * ###################################################################################
       */
        public List<string> listDeg(int y)
        {
            List<string> list = new List<string>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            string year = y.ToString();
            string sql =
                    "SELECT informatica_grau.designacao, COUNT (DISTINCT docente_id) AS Tot " +
                    "FROM informatica_registodocencia " +
                    "INNER JOIN informatica_grau on " +
                    "informatica_registodocencia.grau_id = informatica_grau.id " +
                    "WHERE informatica_registodocencia.ano = " + year + " " +
                    "GROUP BY informatica_grau.designacao " +
                    "ORDER by Tot DESC " +
                    "LIMIT 3";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string degreeName = reader.GetString(0);
                list.Add(degreeName);
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;

            return list;
        } //end of listDeg



        /*
         * ###################################################################################
         * Function to get the name of the degrees by name
         * ###################################################################################
        */
        public List<string> listDegByName(int y, string degName)
        {
            List<string> list = new List<string>();

            string docDegree = '"' + degName + '"';

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            string year = y.ToString();
            string sql =
                    "SELECT informatica_grau.designacao, COUNT (DISTINCT docente_id) AS Tot " +
                    "FROM informatica_registodocencia " +
                    "INNER JOIN informatica_grau on " +
                    "informatica_registodocencia.grau_id = informatica_grau.id " +
                    "WHERE informatica_registodocencia.ano = " + year + " and informatica_grau.designacao like " + docDegree + " " +
                    "GROUP BY informatica_grau.designacao";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string degreeName = reader.GetString(0);
                list.Add(degreeName);
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;

            return list;
        } //end of listDegByName


        /*
         * #########################################################################################
         * Function to get the name of the 3 first establishement with more teahcers by some degree
         * if you want all the establishements remove LIMIT 3 from the end of the query
         * #########################################################################################
        */
        public List<string> listEstByDegName(int y, string degName)
        {
            List<string> list = new List<string>();

            string docDegree = '"' + degName + '"';

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            string year = y.ToString();
            string sql =
                    "SELECT DISTINCT  informatica_estabelecimento.designacao, COUNT (DISTINCT docente_id) AS Tot  " +
                    "FROM informatica_registodocencia " +
                    "INNER JOIN informatica_grau on " +
                    "informatica_registodocencia.grau_id = informatica_grau.id " +
                    "INNER JOIN informatica_estabelecimento on " +
                    "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                    "WHERE informatica_registodocencia.ano = " + year + " and informatica_grau.designacao like " + docDegree + " " +
                    "GROUP BY informatica_estabelecimento.designacao " +
                    "ORDER by Tot DESC " +
                    "LIMIT 3";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string degreeName = reader.GetString(0);
                list.Add(degreeName);
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;

            return list;
        } //end of listEstByDegName



        /*
         * ###################################################################################
         * Function to get the list of establishments by name
         * ###################################################################################
        */
        public List<string> listEstabY(int y)
        {
            List<string> list = new List<string>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            string year = y.ToString();
            string sql =
                    "SELECT DISTINCT "+
                    "informatica_estabelecimento.designacao "+
                    "FROM "+
                      "informatica_registodocencia "+
                      "INNER JOIN informatica_estabelecimento ON (informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id) "+
                    "WHERE "+
                      "informatica_registodocencia.ano = "+ year + " " +
                    "ORDER BY informatica_estabelecimento.designacao";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string estabName = reader.GetString(0);
                list.Add(estabName);
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;

            return list;
        } //end of listEstabY



        /*
         * ####################################################################################
         * Function to get the number of teachers per establisment
         * ####################################################################################
        */
        public Dictionary<string, int> numberTeachEstY(int y)
        {
            Dictionary<string, int> list = new Dictionary<string, int>();

            Console.Write(".");

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();


            list.Clear();
            string year = y.ToString();
            string sql = "SELECT informatica_estabelecimento.designacao, COUNT(DISTINCT docente_id) AS TotDocentes " +
                            "FROM informatica_registodocencia " +
                            "INNER JOIN informatica_estabelecimento on " +
                            "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                            "WHERE informatica_registodocencia.ano = " + year + " " +
                            "GROUP BY informatica_estabelecimento.designacao " +
                            "ORDER BY informatica_estabelecimento.designacao";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string estab = reader.GetString(0);
                Int32 value = reader.GetInt32(1);
                list.Add(estab, value);
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            Console.Write(".");
            return list;
        } //end of numberTeachEstY



        /*
         * ###################################################################################
         * Function to get the list of teachers by one establishment
         * ###################################################################################
        */
        public List<string> listTeachOneEY(int y, string est)
        {
            List<string> list = new List<string>();
            Console.Write(".");

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            string year = y.ToString();
            string estab = '"' + est + '"';
            string sql =
                    "SELECT DISTINCT " +
                      "informatica_docente.nome " +
                    "FROM " +
                      "informatica_registodocencia " +
                      "INNER JOIN informatica_estabelecimento ON " +
                      "(informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id) " +
                      "INNER JOIN informatica_docente ON " +
                      "(informatica_registodocencia.docente_id = informatica_docente.id) " +
                    "WHERE informatica_registodocencia.ano = " + year + " AND " +
                    "informatica_estabelecimento.designacao = " + estab;
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string teachName = reader.GetString(0);
                list.Add(teachName);
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            Console.Write(".");
            return list;
        } //end of listTeachOneEY



        /*
         * ###################################################################################
         * Function to get the list of teachers by establishments
         * ###################################################################################
        */
        public Dictionary<string, List<string>> listTeachEstY(int y)
        {
            Dictionary<string, List<string>> teachByEstY = new Dictionary<string, List<string>>();


            List<string> estList = listEstabY(y);

            foreach (string estabName in estList)
            {
                List<string> teachList = listTeachOneEY(y, estabName);

                teachByEstY.Add(estabName, teachList);
            }

            return teachByEstY;
        } //end of listTeachEstY



        /*
         * ####################################################################################
         * Function to get the lis of teachers and corresponding establisment id
         * ####################################################################################
        */
        public List<MyDict> listTeachEstIdY(int y)
        {
            List<MyDict> list = new List<MyDict>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();


            list.Clear();
            string year = y.ToString();
            string sql = "SELECT DISTINCT " +
                              "informatica_docente.nome, " +
                              "informatica_estabelecimento.designacao " +
                            "FROM " +
                              "informatica_registodocencia " +
                              "INNER JOIN informatica_estabelecimento ON (informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id) " +
                              "INNER JOIN informatica_docente ON (informatica_registodocencia.docente_id = informatica_docente.id) " +
                            "WHERE informatica_registodocencia.ano = " + year + " " +
                            "ORDER BY informatica_docente.nome";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string teacherName = reader.GetString(0);
                string estabId = reader.GetString(1);
                MyDict myDict = new MyDict(teacherName, estabId);
                list.Add(myDict);
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            Console.WriteLine("List from year 200" + year + " loaded");
            return list;
        } //end of listTeachEstIdY



        /*
         * ###################################################################################
         * Function to get the list of the 10 establishments by name
         * ###################################################################################
        */
        public List<string> listFirstEstabY(int y)
        {
            List<string> list = new List<string>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            string year = y.ToString();
            string sql =
                    "SELECT DISTINCT  informatica_estabelecimento.designacao, COUNT (DISTINCT docente_id) AS Tot  " +
                    "FROM informatica_registodocencia " +
                    "INNER JOIN informatica_estabelecimento on " +
                    "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                    "WHERE informatica_registodocencia.ano = " + year + " " +
                    "GROUP BY informatica_estabelecimento.designacao " +
                    "ORDER by Tot DESC " +
                    "LIMIT 10";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader();

            while (reader.Read())
            {
                string estabName = reader.GetString(0);
                list.Add(estabName);
            }

            reader.Close();
            reader = null;

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;

            return list;
        } //end of listFirstEstabY



        /*
         * ####################################################################################
         * Function to get the lis of teachers and corresponding category per establishment
         * ####################################################################################
        */
        public Dictionary<string, List<MyDict>> listTeachCatEtY(int y)
        {
            
            Dictionary<string, List<MyDict>> dicTCatByEst = new Dictionary<string, List<MyDict>>();
            List<string> listEstab = listFirstEstabY(y);

            foreach (string est in listEstab)
            {
                List<MyDict> list = new List<MyDict>();

                string connectionString = "URI=file:database/pristine.db,version=3";
                IDbConnection dbcon;
                dbcon = (IDbConnection)new SqliteConnection(connectionString);
                dbcon.Open();
                IDbCommand dbcmd = dbcon.CreateCommand();

                string estab = '"' + est + '"';
                Console.WriteLine(estab);
                string year = y.ToString();
                string sql = "SELECT DISTINCT " +
                                  //"informatica_estabelecimento.designacao, " +
                                  "informatica_categoria.designacao, " +
                                  "informatica_docente.nome " +
                                "FROM informatica_registodocencia " +
                                  "INNER JOIN informatica_estabelecimento ON (informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id) " +
                                  "INNER JOIN informatica_docente ON (informatica_registodocencia.docente_id = informatica_docente.id) " +
                                  "INNER JOIN informatica_categoria ON (informatica_registodocencia.categoria_id = informatica_categoria.id) " +
                                "WHERE informatica_registodocencia.ano = " + year + " AND informatica_estabelecimento.designacao = " + estab;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string teacherName = reader.GetString(1);
                    string teachCat = reader.GetString(0);
                    list.Add(new MyDict(teacherName, teachCat));
                }

                dicTCatByEst.Add(estab, list);
                Console.WriteLine(estab + " Ok");
                reader.Close();
                reader = null;

                dbcmd.Dispose();
                dbcmd = null;
                dbcon.Close();
                dbcon = null;
            }
            Console.WriteLine("Dictionary from year 200" + y + " loaded");
            return dicTCatByEst;
        } //end of listTeachCatEtY

    } //end of class subFuncions

} //end of namespace
